PostgreSQL TPROC-C Benchmarks: PostgreSQL 12 vs. PostgreSQL 13 Performance

PostgreSQL

The tests were run on both PostgreSQL 12.4 and PostgreSQL 13.0 from the community packages. Each server was tested mostly untuned and tuned.

"Untuned" Settings

All other settings remained at the default values.

Tuned Settings

All other settings remained at the default values.

HammerDB

The test driver used is HammerDB with a few modifications to the data model to use appropriate data types for PostgreSQL, optimize column order for alignment, and remove fill factor settings (see the Data Model section at the end of this post for the exact schema used). These modifications are in accordance with the TPC-C specification:

Section 1.3 — Table Layouts

For each table, the following list of attributes can be implemented in any order, using any physical representation available from the tested system.

No changes were made to the algorithms used by HammerDB.

The database is populated by HammerDB with 2000 warehouses. Each table is CLUSTERed by its primary key index (except for history which has no indexes). ANALYZE and VACUUM FREEZE are run on all tables. A base backup of this pristine database is preserved.

Method

For each number of virtual users, a new database is restored from the base backup. HammerDB is then run with a ramp up time of 5 minutes and test time of 1 hour. 

Note that think and key times are disabled, as the intention with this test is to see the maximum possible performance. This does not reflect a real-world workload, nor is it appropriate if running a "by the book" TPC-C benchmark..
Note: for a proper TPC-C benchmark, the test must be run for a minimum of two hours.

Results

The following table shows the results for 12.4 and 13.0 both out of the box and properly tuned. The values indicate transactions per minute.

We can see from this chart that the untuned performance of 12.4 and 13.0 are mostly identical, with 13.0 having the edge, and performance maxes out between 50 and 75 virtual users.

When properly tuned, we can achieve up to eight times the number of transactions per minute, and sustain that as the number of virtual users increases.

The same can be seen between 13.0 untuned and tuned.

If we compare 12.4 and 13.0 with proper tuning, we can see a near 20% improvement in transactions per minute at 250 virtual users before slowly dropping back down to 12.4 levels.

Summary

The tests show that there is immense benefit to properly tuning your PostgreSQL server.  Version 13.0 performs better than 12.4 out of the box, and the difference is even more striking when both servers are tuned.
PostgreSQL 13 represents another leap forward for Open Source relational databases, as it continues to improve with every new release.

Appendix A: Data Model

CREATE TABLE customer (
    c_since timestamp with time zone NOT NULL,
    c_id integer NOT NULL,
    c_w_id integer NOT NULL,
    c_d_id smallint NOT NULL,
    c_payment_cnt smallint NOT NULL,
    c_delivery_cnt smallint NOT NULL,
    c_first character varying(16) NOT NULL,
    c_middle character(2) NOT NULL,
    c_last character varying(16) NOT NULL,
    c_street_1 character varying(20) NOT NULL,
    c_street_2 character varying(20) NOT NULL,
    c_city character varying(20) NOT NULL,
    c_state character(2) NOT NULL,
    c_zip character(9) NOT NULL,
    c_phone character(16) NOT NULL,
    c_credit character(2) NOT NULL,
    c_credit_lim numeric(12,2) NOT NULL,
    c_discount numeric(4,4) NOT NULL,
    c_balance numeric(12,2) NOT NULL,
    c_ytd_payment numeric(12,2) NOT NULL,
    c_data character varying(500) NOT NULL,

    CONSTRAINT customer_i1 PRIMARY KEY (c_w_id, c_d_id, c_id)
);
CREATE UNIQUE INDEX customer_i2 ON customer USING btree (c_w_id, c_d_id, c_last, c_first, c_id);

CREATE TABLE district (
    d_w_id integer NOT NULL,
    d_next_o_id integer NOT NULL,
    d_id smallint NOT NULL,
    d_ytd numeric(12,2) NOT NULL,
    d_tax numeric(4,4) NOT NULL,
    d_name character varying(10) NOT NULL,
    d_street_1 character varying(20) NOT NULL,
    d_street_2 character varying(20) NOT NULL,
    d_city character varying(20) NOT NULL,
    d_state character(2) NOT NULL,
    d_zip character(9) NOT NULL,

    CONSTRAINT district_i1 PRIMARY KEY (d_w_id, d_id)
);

CREATE TABLE history (
    h_date timestamp with time zone NOT NULL,
    h_c_id integer,
    h_c_w_id integer NOT NULL,
    h_w_id integer NOT NULL,
    h_c_d_id smallint NOT NULL,
    h_d_id smallint NOT NULL,
    h_amount numeric(6,2) NOT NULL,
    h_data character varying(24) NOT NULL
);

CREATE TABLE item (
    i_id integer NOT NULL,
    i_im_id integer NOT NULL,
    i_name character varying(24) NOT NULL,
    i_price numeric(5,2) NOT NULL,
    i_data character varying(50) NOT NULL,

    CONSTRAINT item_i1 PRIMARY KEY (i_id)
);

CREATE TABLE new_order (
    no_w_id integer NOT NULL,
    no_o_id integer NOT NULL,
    no_d_id smallint NOT NULL,

    CONSTRAINT new_order_i1 PRIMARY KEY (no_w_id, no_d_id, no_o_id)
);

CREATE TABLE order_line (
    ol_delivery_d timestamp with time zone,
    ol_o_id integer NOT NULL,
    ol_w_id integer NOT NULL,
    ol_i_id integer NOT NULL,
    ol_supply_w_id integer NOT NULL,
    ol_d_id smallint NOT NULL,
    ol_number smallint NOT NULL,
    ol_quantity smallint NOT NULL,
    ol_amount numeric(6,2),
    ol_dist_info character(24),

    CONSTRAINT order_line_i1 PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)
);

CREATE TABLE orders (
    o_entry_d timestamp with time zone NOT NULL,
    o_id integer NOT NULL,
    o_w_id integer NOT NULL,
    o_c_id integer NOT NULL,
    o_d_id smallint NOT NULL,
    o_carrier_id smallint,
    o_ol_cnt smallint NOT NULL,
    o_all_local smallint NOT NULL,

    CONSTRAINT orders_i1 PRIMARY KEY (o_w_id, o_d_id, o_id)
);
CREATE UNIQUE INDEX orders_i2 ON orders USING btree (o_w_id, o_d_id, o_c_id, o_id);

CREATE TABLE stock (
    s_i_id integer NOT NULL,
    s_w_id integer NOT NULL,
    s_ytd integer NOT NULL,
    s_quantity smallint NOT NULL,
    s_order_cnt smallint NOT NULL,
    s_remote_cnt smallint NOT NULL,
    s_dist_01 character(24) NOT NULL,
    s_dist_02 character(24) NOT NULL,
    s_dist_03 character(24) NOT NULL,
    s_dist_04 character(24) NOT NULL,
    s_dist_05 character(24) NOT NULL,
    s_dist_06 character(24) NOT NULL,
    s_dist_07 character(24) NOT NULL,
    s_dist_08 character(24) NOT NULL,
    s_dist_09 character(24) NOT NULL,
    s_dist_10 character(24) NOT NULL,
    s_data character varying(50) NOT NULL,

    CONSTRAINT stock_i1 PRIMARY KEY (s_w_id, s_i_id)
);

CREATE TABLE warehouse (
    w_id integer NOT NULL,
    w_name character varying(10) NOT NULL,
    w_street_1 character varying(20) NOT NULL,
    w_street_2 character varying(20) NOT NULL,
    w_city character varying(20) NOT NULL,
    w_state character(2) NOT NULL,
    w_zip character(9) NOT NULL,
    w_tax numeric(4,4) NOT NULL,
    w_ytd numeric(12,2) NOT NULL,

    CONSTRAINT warehouse_i1 PRIMARY KEY (w_id)
);